package dao;

import entity.Music;
import util.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MusicDao {
    /**
     * 上传音乐
     * @param title
     * @param singer
     * @param time
     * @param url
     * @param userid
     * @return
     */
    public int insert(String title, String singer, String time, String url,
                      int userid) {

        Connection con = null;
        PreparedStatement prs = null;

        try {
            con = DBUtils.getConn();
            String sql = "insert into music(title,singer,time,url,userid) values (?,?,?,?,?)";
            prs = con.prepareStatement(sql);
            prs.setString(1,title);
            prs.setString(2,singer);
            prs.setString(3,time);
            prs.setString(4,url);
            prs.setInt(5,userid);

            int ret = prs.executeUpdate();
            if(ret == 0) {
                return 0;//失败
            }
            return ret;
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,prs,null);
        }
        return 0;
    }


    /**
     * 查询全部歌单
     */
    public List<Music> findMusic(){
        Connection con = null;
        PreparedStatement prs = null;
        ResultSet resultSet = null;

        List<Music> musicList = new ArrayList<>();

        try {
            con = DBUtils.getConn();
            String sql = "select  * from music";
            prs = con.prepareStatement(sql);

            resultSet = prs.executeQuery();

            while(resultSet.next()) {
                Music music = new Music();
                music.setId(resultSet.getInt("id"));
                music.setTitle(resultSet.getString("title"));
                music.setSinger(resultSet.getString("singer"));
                music.setTime(resultSet.getString("time"));
                music.setUrl(resultSet.getString("url"));
                music.setUserId(resultSet.getInt("userid"));
                musicList.add(music);
            }

        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,prs,resultSet);
        }
        return musicList;
    }

    /**
     * 根据id查找音乐
     * @param id
     * @return
     */
    public  Music findMusicById(int id){
        Connection con = null;
        PreparedStatement prs = null;
        ResultSet resultSet = null;
        Music music = null;
        try {
            con = DBUtils.getConn();
            String sql = "select  * from music where id=?";
            prs = con.prepareStatement(sql);
            prs.setInt(1,id);

            resultSet = prs.executeQuery();
            if(resultSet.next()) {
                music = new Music();
                music.setId(resultSet.getInt("id"));
                music.setTitle(resultSet.getString("title"));
                music.setSinger(resultSet.getString("singer"));
                music.setTime(resultSet.getString("time"));
                music.setUrl(resultSet.getString("url"));
                music.setUserId(resultSet.getInt("userid"));
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,prs,resultSet);
        }
        return music;
    }

    //单元测试 0-》JUnit
/*
    public static void main(String[] args) {
        MusicDao musicDao = new MusicDao();
        Music music = musicDao.findMusicById(2);
        System.out.println(music);
    }
*/


    /**
     * 根据关键字查询歌单
     */
    public List<Music> findMusicByKey(String str){
        Connection con = null;
        PreparedStatement prs = null;
        ResultSet resultSet = null;

        List<Music> musicList = new ArrayList<>();

        try {
            con = DBUtils.getConn();
            //String sql = "select  * from music where title like '%"+ str+ "%'";
            String sql = "select  * from music where title like ?";
            prs = con.prepareStatement(sql);
            str = "%"+ str+ "%";
            prs.setString(1,str);

            System.out.println("sql: " + prs.toString());

            resultSet = prs.executeQuery();

            while(resultSet.next()) {
                Music music = new Music();
                music.setId(resultSet.getInt("id"));
                music.setTitle(resultSet.getString("title"));
                music.setSinger(resultSet.getString("singer"));
                music.setTime(resultSet.getString("time"));
                music.setUrl(resultSet.getString("url"));
                music.setUserId(resultSet.getInt("userid"));
                musicList.add(music);
            }

        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,prs,resultSet);
        }
        return musicList;
    }

 /*   public static void main(String[] args) {
        MusicDao musicDao = new MusicDao();
        List<Music> musicList = musicDao.findMusicByKey("青");
        for (Music music: musicList) {
            System.out.println(music);
        }
    }*/


    /**
     * 删除歌曲：
     */
    public  int deleteMusicById(int musicId) {
        Connection con = null;
        PreparedStatement prs = null;

        try {
            con = DBUtils.getConn();
            String sql = "delete from music where id=?";
            prs = con.prepareStatement(sql);
            prs.setInt(1,musicId);

            int ret = prs.executeUpdate();
            if(ret == 1) {
                if(findLoveMusicById(musicId)) {
                    int ret2 = deleteLoveMusicById(musicId);
                    if(ret2 == 1) {
                        System.out.println("删除成功了!");
                        return 1;
                    }
                }
                return 1;
            }else {
                return 0;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,prs,null);
        }
        return 0;
    }

    /**
     * 看中间表是否有该id的音乐数据
     */
    public boolean findLoveMusicById(int musicId) {
        Connection con = null;
        PreparedStatement pres = null;
        ResultSet resultSet = null;
        try {
            con = DBUtils.getConn();
            String sql = "select * from lovemusic where music_id=?";
            pres = con.prepareStatement(sql);
            pres.setInt(1,musicId);
            resultSet = pres.executeQuery();
            if(resultSet.next()) {
                return true;
            }

        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,pres,resultSet);
        }
        return false;
    }

    /**
     * 当删除服务器上的音乐时，同时在我喜欢的列表的数据库中进行删除。
     * @param musicId
     * @return
     */
    public int deleteLoveMusicById(int musicId) {
        Connection con = null;
        PreparedStatement pres = null;
        try {
            con = DBUtils.getConn();
            String sql = "delete from lovemusic where music_id=?";
            pres = con.prepareStatement(sql);
            pres.setInt(1,musicId);
            int ret = pres.executeUpdate();
            if(ret == 1) {
                return 1;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,pres,null);
        }
        return 0;
    }
}
